﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Data.Common;
using Data.Enum;
using Data.Model;
using Data.ViewModel;

namespace DataAccess.BikeManage
{
    /// <summary>
    /// 控制箱管理类
    /// </summary>
    public class ControlBox : DbBase, IControlBox
    {
        /// <summary>
        /// 获取集中控制箱，获取一个区域下的所有控制箱列表
        /// </summary>
        /// <param name="areaId">区域的id</param>
        /// <returns></returns>
        public ResultInfo<IList<jsh_controlbox>> GetControlBox(int areaId)
        {
            var result = new ResultInfo<IList<jsh_controlbox>>();
            try
            {
                using (var command = GetProCommand())
                {
                    command.CommandText = "sp_GetControlBox";
                    command.Parameters.Add("@areaid", SqlDbType.Int).Value = areaId;

                    var ds = new DataSet();
                    var da = new SqlDataAdapter(command);
                    da.Fill(ds);

                    IList<jsh_controlbox> boxex = (from DataRow d in ds.Tables[0].Rows select new jsh_controlbox(d)).ToList();
                    result.Obj = boxex;
                }
            }

            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;
        }

        /// <summary>
        /// 通过控制箱id获取控制箱的信息
        /// </summary>
        /// <param name="id">控制箱id</param>
        /// <returns></returns>
        public ResultInfo<jsh_controlbox> GetControlBoxById(int id)
        {
            var result = new ResultInfo<jsh_controlbox> { ResultNumber = ResultEnum.Error };
            try
            {
                var b = new jsh_controlbox();
                if (id > 0)
                {
                    var sql = string.Format("select * from jsh_controlbox where id={0}", id);
                    var ds = ExcuteSqlDataSet(sql);
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        b = new jsh_controlbox(ds.Tables[0].Rows[0]);

                    }
                    else
                    {
                        result.ResultNumber = ResultEnum.Error;
                        result.Describe = "不存在该控制箱";
                    }
                    result.Obj = b;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "查询控制箱失败";
                }

            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="areaId"></param>
        /// <param name="date"></param>
        /// <param name="timeZone"></param>
        /// <returns></returns>
        public ResultInfo<IList<jsh_controlbox>> GetControlBox(int areaId, string date, string timeZone)
        {
            var result = new ResultInfo<IList<jsh_controlbox>>();
            try
            {
                using (var command = GetProCommand())
                {
                    command.CommandText = "sp_GetControlBoxByDate";
                    command.Parameters.Add("@areaid", SqlDbType.Int).Value = areaId;

                    var ds = new DataSet();
                    var da = new SqlDataAdapter(command);
                    da.Fill(ds);

                    IList<jsh_controlbox> boxex = (from DataRow d in ds.Tables[0].Rows select new jsh_controlbox(d)).ToList();
                    result.Obj = boxex;
                }
            }

            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;
        }


        /// <summary>
        /// 获取控制箱
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="boxName">控制箱的名称</param>
        /// <returns></returns>
        public ResultInfo<PagingDataSet<ControlBoxView>> GetControlBoxs(int pageIndex, int pageSize, string boxName)
        {
            var result = new ResultInfo<PagingDataSet<ControlBoxView>>();
            try
            {
                using (SqlCommand myCommand = GetProCommand())
                {
                    myCommand.CommandText = "Common_GetPagingRecords";
                    var fromClause = new StringBuilder();
                    fromClause.Append("jsh_controlbox with (nolock) ");
                    var whereClause = new StringBuilder();
                    if (!string.IsNullOrEmpty(boxName))
                    {
                        whereClause.Append(string.Format(" and name like '%{0}%' ", boxName));
                    }
                    if (whereClause.Length > 3)
                    {
                        whereClause.Remove(0, 5);
                    }

                    myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex - 1;
                    myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                    myCommand.Parameters.Add("@FromClause", SqlDbType.VarChar, 1024).Value = fromClause.ToString();
                    myCommand.Parameters.Add("@SelectFields", SqlDbType.VarChar, 512).Value = "*";
                    myCommand.Parameters.Add("@WhereClause", SqlDbType.NVarChar, 4000).Value = whereClause.ToString();
                    myCommand.Parameters.Add("@SortField", SqlDbType.VarChar, 256).Value = "id";
                    myCommand.Parameters.Add("@SortFieldIsDesc", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@MaxRecords", SqlDbType.Int).Value = int.MaxValue;
                    myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                    myCommand.Parameters.Add("@ReverseOrder", SqlDbType.Bit).Direction = ParameterDirection.Output;

                    var list = new List<jsh_controlbox>();

                    var dataSet = new DataSet();
                    var da = new SqlDataAdapter(myCommand);
                    da.Fill(dataSet);

                    if (dataSet != null)
                    {
                        foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                        {
                            var temp = new jsh_controlbox(dataRow);
                            if (temp.TranByConstructFlag)
                            {
                                list.Add(temp);
                            }
                        }
                    }

                    int totalRecords = Convert.ToInt32(myCommand.Parameters["@TotalRecords"].Value);
                    bool reverseOrder = Convert.ToBoolean(myCommand.Parameters["@ReverseOrder"].Value);

                    if (reverseOrder)
                        list.Reverse();

                    var listView = PopulateControlBox(list);

                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = new PagingDataSet<ControlBoxView> { PageIndex = pageIndex, PageSize = pageSize, Records = listView, TotalRecords = totalRecords };
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 更新控制箱
        /// </summary>
        /// <param name="controlbox">控制箱的详细信息，如果有id就是更新这个控制箱，如果没有id就是添加一条新记录</param>
        /// <returns></returns>
        public ResultInfo<int> CreateOrUpdateControlBox(jsh_controlbox controlbox)
        {
            var result = new ResultInfo<int>();
            try
            {
                string sql = string.Empty;
                if (controlbox != null)
                {
                    if (controlbox.id > 0)
                    {
                        //TODO:update sql string
                        sql = string.Format("UPDATE jsh_controlbox SET [Name]='{0}',[IP]='{1}',[PSAMNO]='{2}',[PileNUM]={3},[BikeNUM]={4},[AlertNUM]={5},[ADDRESS]='{6}',[AreaID]={7},[Area]='{8}',[Ver]='{9}',[Status]='{10}' where [id]={11} select {11} id",controlbox.Name,controlbox.Ip,controlbox.Psamno,controlbox.Pilenum,controlbox.Bikenum,controlbox.Alertnum,controlbox.Address,controlbox.Areaid,controlbox.Area,controlbox.Ver,controlbox.Status,controlbox.id);
                    }
                    else
                    {
                        string check = string.Format("select id from jsh_controlbox where id='{0}'", controlbox.id);
                        DataSet dt = ExcuteSqlDataSet(check);
                        if (dt != null && dt.Tables[0].Rows.Count > 0)
                        {
                            result.ResultNumber = ResultEnum.Error;
                            result.Describe = "已经存在同名控制箱，请重新输入";
                        }
                        else
                        {
                            sql = string.Format("insert into jsh_controlbox([Name],[IP],[PSAMNO],[PileNUM],[BikeNUM],[AlertNUM],[ADDRESS],[AreaID],[Area],[Ver],[Status]) values('{0}','{1}','{2}',{3},{4},{5},'{6}',{7},'{8}','{9}','{10}') select @@identity id", controlbox.Name, controlbox.Ip, controlbox.Psamno, controlbox.Pilenum, controlbox.Bikenum, controlbox.Alertnum, controlbox.Address, controlbox.Areaid, controlbox.Area, controlbox.Ver, controlbox.Status);
                        }
                    }
                    if (!string.IsNullOrEmpty(sql))
                    {
                        DataSet dataSet = ExcuteSqlDataSet(sql);
                        if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
                        {
                            int temp = Convert.ToInt32(dataSet.Tables[0].Rows[0]["id"]);
                            if (temp > 0)
                            {
                                result.ResultNumber = ResultEnum.Success;
                                result.Obj = temp;
                            }
                            else
                            {
                                result.ResultNumber = ResultEnum.Error;
                                result.Describe = "更新数据失败";
                            }
                        }
                    }
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "请输入有效数据";
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 删除控制箱
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResultInfo<bool> DeleteControlBox(int id)
        {
            var result = new ResultInfo<bool>();
            try
            {
                string sql = string.Format("delete from jsh_controlbox where id={0}", id);
                int temp = ExcuteSql(sql);
                if (temp > 0)
                {
                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = true;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Obj = false;
                }
            }
            catch (Exception)
            {

            }
            return result;
        }

        private static List<ControlBoxView> PopulateControlBox(IEnumerable<jsh_controlbox> boxs)
        {
            int i = 0;
            var boxes = new List<ControlBoxView>();
            foreach (jsh_controlbox box in boxs)
            {
                i++;
                boxes.Add(new ControlBoxView
                {
                    Serial = i,
                    Id = box.id,
                    BikeCount = box.Bikenum,
                    BoxNo = box.Name,
                    LockPileNum = box.Pilenum,
                    Status = box.Status,
                    Address = box.Address,
                    IPAddress = box.Ip,
                    Version = box.Ver,
                    Bloc = box.Area
                });
            }
            return boxes;
        }

    }
}
